--    Author    : MAYANTING
--    Name      : ADM.RPT_SUST_DLMG_WRATE_DL_DATE.HQL
--    Functions : 表15：存、贷款加权平均利率及利差时序表
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-06-11  MAYANTING           1.CREATE THE PROCEDURE
--
INSERT OVERWRITE TABLE ADM.RPT_SUST_DLMG_WRATE_DL_DATE PARTITION (DATA_DATE = '#V_DATA_DATE#')
    SELECT  AREA.AREA_CODE_4                          AS FIN_ORG_DIST -- 金融机构地区
           ,T.CCY                                     AS CCY          -- 贷款币种
           ,T.TYPE                                    AS TYPE         -- 统计类型
           ,T.TYPE_DESC                               AS TYPE_DESC    -- 统计类型描述
           ,SUM(T.LOAN_SUM )                          AS LOAN_SUM     -- 贷款额          不需要处理单位,页面为加权平均利率和利差
           ,SUM(T.LOAN_WSUM)                          AS LOAN_WSUM    -- 贷款额加权值    不需要处理单位,页面为加权平均利率和利差
           ,SUM(T.DEP_SUM  )                          AS DEP_SUM      -- 存款额          不需要处理单位,页面为加权平均利率和利差
           ,SUM(T.DEP_WSUM )                          AS DEP_WSUM     -- 存款额加权值    不需要处理单位,页面为加权平均利率和利差
    FROM(
        SELECT LL.FIN_ORG_NO                          AS FIN_ORG_NO
              ,LL.CCY                                 AS CCY
              ,'0'                                    AS TYPE           -- 统计类型
              ,'按当月发生业务计算'                   AS TYPE_DESC      -- 统计类型描述
              ,LL.LOAN_SUM                            AS LOAN_SUM
              ,LL.LOAN_WSUM                           AS LOAN_WSUM
              ,LL.DEP_SUM                             AS DEP_SUM
              ,LL.DEP_WSUM                            AS DEP_WSUM
        FROM(
             SELECT FSE.FIN_ORG_NO                    AS FIN_ORG_NO
                   ,FSE.CCY                           AS CCY
                   ,COALESCE(FSE.OCCUR_AMOUNT,0)      AS LOAN_SUM
                   ,COALESCE(FSE.WSUM_AMOUNT ,0)      AS LOAN_WSUM
                   ,0                                 AS DEP_SUM
                   ,0                                 AS DEP_WSUM
             FROM EDW.DS_LOAF_SUM FSE -- 贷款发生额
             WHERE FSE.DATA_DATE='#V_DATA_DATE#' AND FSE.DRAWDOWN_OR_REPAY='1' -- 贷款发放收回标志 1-发放,0-收回

             UNION ALL

             SELECT DEPF.FIN_ORG_NO                   AS FIN_ORG_NO
                   ,DEPF.CCY                          AS CCY
                   ,0                                 AS LOAN_SUM
                   ,0                                 AS LOAN_WSUM
                   ,COALESCE(DEPF.ACTUAL_AMOUNT,0)    AS DEP_SUM
                   ,COALESCE(DEPF.WSUM_AMOUNT  ,0)    AS DEP_WSUM
             FROM EDW.DS_DEPB_SUM DEPF -- 存款发生额
             WHERE DEPF.DATA_DATE='#V_DATA_DATE#'
             ) LL
        UNION ALL

        SELECT CL.FIN_ORG_NO                          AS FIN_ORG_NO
              ,CL.CCY                                 AS CCY
              ,'1'                                    AS TYPE           -- 统计类型
              ,'按当月存量计算'                       AS TYPE_DESC      -- 统计类型描述
              ,CL.LOAN_SUM                            AS LOAN_SUM
              ,CL.LOAN_WSUM                           AS LOAN_WSUM
              ,CL.DEP_SUM                             AS DEP_SUM
              ,CL.DEP_WSUM                            AS DEP_WSUM
        FROM(
             SELECT YE.FIN_ORG_NO                     AS FIN_ORG_NO
                   ,YE.CCY                            AS CCY
                   ,COALESCE(YE.ACTUAL_BAL,0)         AS LOAN_SUM
                   ,COALESCE(YE.WSUM_BAL  ,0)         AS LOAN_WSUM
                   ,0                                 AS DEP_SUM
                   ,0                                 AS DEP_WSUM
             FROM EDW.DS_LOAB_SUM YE -- 贷款余额
             WHERE YE.DATA_DATE='#V_DATA_DATE#'

             UNION ALL

             SELECT DEP.FIN_ORG_NO                    AS FIN_ORG_NO
                   ,DEP.CCY                           AS CCY
                   ,0                                 AS LOAN_SUM
                   ,0                                 AS LOAN_WSUM
                   ,COALESCE(DEP.ACTUAL_BAL,0)        AS DEP_SUM
                   ,COALESCE(DEP.WSUM_BAL  ,0)        AS DEP_WSUM
             FROM EDW.DS_DEPB_SUM DEP -- 存款余额
             WHERE DEP.DATA_DATE='#V_DATA_DATE#'
             ) CL
    ) T
    -- 与地区表最细级关联
    LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON T.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
    LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
    GROUP BY AREA.AREA_CODE_4   -- 金融机构地区
            ,T.CCY              -- 贷款币种
            ,T.TYPE             -- 统计类型
            ,T.TYPE_DESC        -- 统计类型描述
    ;